﻿using BLL;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Web.UI.WebControls;

namespace Thksoft.Module.CustomerManage
{
    public partial class Bank : BaseUI.BasePage
    {
        private Sys_CompanyBLL companyBLL = new Sys_CompanyBLL();
        private Sys_BankBLL bankBLL = new Sys_BankBLL();
        private Yx_CustomerBLL customerBLL = new Yx_CustomerBLL();
        private Yx_CustomerBankBLL customerBankBLL = new Yx_CustomerBankBLL();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["WHERE"] = null;
                BindData(true);
                //初始化公司信息
                BindCompany();
            }
        }

        private void BindCompany()
        {
            DataTable dtCompany;
            if (lognUser.USER_LOGINNAME == "SUPER")
            {
                dtCompany = companyBLL.GetAllInfo();
            }
            else
            {
                dtCompany = companyBLL.GetInfoByPId((int)CurrentUserCompanyID);
            }
            FORM_COMPANY_ID.DataSource = dtCompany;
            FORM_COMPANY_ID.DataTextField = "CMY_NAME";
            FORM_COMPANY_ID.DataValueField = "CMY_ID";
            FORM_COMPANY_ID.DataBind();
            FORM_COMPANY_ID.Items.Insert(0, new ListItem("---请选择---", "-1"));
            FORM_COMPANY_ID.SelectedValue = CurrentUserCompanyID.ToString();
        }

        private void BindData(bool iffirst)
        {
            if (iffirst)
            {
                PageIng.CurrentPage = 1;
            }
            string where = null;
            if (ViewState["WHERE"] != null)
            {
                where = ViewState["WHERE"] as string;
            }
            else
            {
                where = " T2.N_COMPANY_ID=" + CurrentUserCompanyID;/*@" T2.N_COMPANY_ID in
       (SELECT CMY_ID
          FROM SYS_COMPANY T
         START WITH CMY_ID = " + CurrentUserCompanyID + @"
        CONNECT BY PRIOR CMY_ID = CMY_PARENTID)";*/
            }

            var sql = @"SELECT
	T1.N_ID,
	T1.N_CUSTOMER_ID,
	T1.C_REALNAME,
	T1.C_NUMBER,
	T1.N_BANK_ID,
	TO_CHAR(
		T1.D_OPERATOR_DATE,
		'yyyy-mm-dd HH24:MI:SS'
	) D_OPERATOR_DATE,
	T1.N_OPERATOR_ID,
	T2.C_NAME AS C_CUSTOMER_NAME,
	CASE T2.N_STATE
		WHEN 0 THEN '停用'
		ELSE '使用'
	END AS C_STATE,
	T3.C_NAME AS C_BANK_NAME,
	T4.USER_NAME AS C_OPERATOR_NAME,
	T5.CMY_NAME AS C_COMPANY_NAME
FROM YX_CUSTOMER_BANK T1
LEFT JOIN YX_CUSTOMER T2 ON T1.N_CUSTOMER_ID = T2.N_ID
LEFT JOIN SYS_BANK T3 ON T1.N_BANK_ID = T3.N_ID
LEFT JOIN SYS_USER T4 ON T1.N_OPERATOR_ID = T4.USER_ID
LEFT JOIN SYS_COMPANY T5 ON T2.N_COMPANY_ID = T5.CMY_ID";
            sql += " WHERE " + where;
            if (iffirst)
            {
                PageIng.RowsCount = companyBLL.SqlCount(sql);
            }
            var dt = companyBLL.PageQuery(sql, (PageIng.CurrentPage - 1) * PageIng.RowsPerPage, PageIng.RowsPerPage);
            if (dt.Rows.Count > 0)
            {
                PageIng.Visible = true;
                PageIng.DataSource = dt;
                PageIng.GridViewSource = grvShow;
                PageIng.RowsPerPage = PageIng._rowsPerPage;
                PageIng.Bind();
            }
            else
            {
                PageIng.DataSource = dt;
                PageIng.GridViewSource = grvShow;
                grvShow.DataSource = null;
                grvShow.DataBind();
                PageIng.Visible = false;
            }
        }

        protected void btnSelect_Click(object sender, EventArgs e)
        {
            string where;
            if (FORM_COMPANY_ID.SelectedValue.ToString() == "-1")
            {
                if (lognUser.USER_LOGINNAME == "SUPER")
                {
                    where = "1 = 1";
                }
                else
                {
                    where = "T2.N_COMPANY_ID IN (" + companyBLL.GetCompanyFatherAndChildByID(CurrentUserCompanyID) + ")";
                }
            }
            else
            {
                where = "T2.N_COMPANY_ID = " + FORM_COMPANY_ID.SelectedValue;
            }
            if (FORM_STATE.SelectedValue != "-1")
            {
                where += " AND T2.N_STATE = " + FORM_STATE.SelectedValue;
            }
            ViewState["WHERE"] = where;
            BindData(true);
        }

        protected void PageIng_DataBinding(object sender, EventArgs e)
        {
            BindData(false);
        }

        protected void btnInsert_Click(object sender, EventArgs e)
        {
            MessageBox.ResponseScript(this, "FORM_INSERT();");
        }

        protected void BtnEdit_Click(object sender, EventArgs e)
        {
            int count = 0;
            for (int i = 0; i < grvShow.Rows.Count; i++)
            {
                if (((CheckBox)grvShow.Rows[i].FindControl("chkSelectId")).Checked == true)
                {
                    count++;
                    if (count == 1)
                    {
                        string pid = ((CheckBox)grvShow.Rows[i].FindControl("chkSelectId")).ToolTip;
                        MessageBox.ResponseScript(this, "FORM_EDIT(\"" + pid + "\");");
                        //string url = "EditCondition.aspx?ID=" + pid;
                        //Response.Redirect(url);
                        return;
                    }
                }
            }
        }

        protected void btnDelele_Click(object sender, EventArgs e)
        {
            int count = 0;
            var idStr = "";
            for (int i = 0; i < grvShow.Rows.Count; i++)
            {
                if (((CheckBox)grvShow.Rows[i].FindControl("chkSelectId")).Checked == true)
                {
                    string pid = ((CheckBox)grvShow.Rows[i].FindControl("chkSelectId")).ToolTip;
                    idStr += pid + ",";
                    count++;
                }
            }
            if (idStr != "")
            {
                idStr = idStr.Substring(0, idStr.Length - 1);
                var sql = "DELETE FROM YX_CUSTOMER_BANK WHERE N_ID IN(" + idStr + ")";
                if (companyBLL.NoQuery(sql) > 0)
                {
                    MessageBox.Show(this, "删除成功");
                    BindData(true);
                }
                else
                {
                    MessageBox.Show(this, "删除失败.");
                }
            }
            else
            {
                MessageBox.Show(this, "请选择需要删除的项！");
            }
            //MessageBox.ResponseScript(this, "FORM_EDIT();");
        }

        /// <summary>
        /// 批量导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnImport(object sender, EventArgs e)
        {
            string path = Request.MapPath(FILE_KEY.Value);

            try
            {
                if (!string.IsNullOrEmpty(path.Trim()))
                {
                    string FileType = System.IO.Path.GetExtension(path).ToLower();
                    if (FileType == ".xls" || FileType == ".xlsx")
                    {
                        #region 将选择文件保存在当面程序运行目录下,并读取Excel为DataSet

                        DataSet ds = ImportExcel.ImportExcelToDataSet(path);

                        #endregion 将选择文件保存在当面程序运行目录下,并读取Excel为DataSet

                        if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count != 0)
                        {
                            DataTable dt = ds.Tables[0];

                            string message = "";

                            int BankCount;
                            int CustomerCount;

                            #region 判断是否有固定列

                            if (!dt.Columns.Contains("客户公司名称"))
                            {
                                MessageBox.Show(Page, "没找到【客户公司名称】列");
                                return;
                            }

                            if (!dt.Columns.Contains("银行类型"))
                            {
                                MessageBox.Show(Page, "没找到【银行类型】列");
                                return;
                            }

                            if (!dt.Columns.Contains("开户账户"))
                            {
                                MessageBox.Show(Page, "没找到【开户账户】列");
                                return;
                            }

                            if (!dt.Columns.Contains("开户姓名"))
                            {
                                MessageBox.Show(Page, "没找到【开户姓名】列");
                                return;
                            }

                            #endregion 判断是否有固定列

                            #region 添加数据

                            List<Yx_CustomerBank> ls_bank = new List<Yx_CustomerBank>();
                            string summessage = "";

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                message = "";

                                var entity = new Yx_CustomerBank();

                                try
                                {
                                    entity.N_CUSTOMER_ID = Convert.ToDecimal(customerBLL.GetData2("AND C_NAME ='" + dt.Rows[i]["客户公司名称"].ToString() + "' AND N_COMPANY_ID =" + lognUser.USER_COMPANY).Rows[0]["N_ID"]);
                                }
                                catch
                                {
                                    message += "第【" + i + 1 + "】行,【客户公司名称】列,内容不正确.";
                                }

                                try
                                {
                                    entity.N_BANK_ID = (bankBLL.GetEntitys2(" T1.C_NAME ='" + dt.Rows[i]["银行类型"].ToString() + "'", null, 0, 1, out BankCount))[0].N_ID;
                                }
                                catch
                                {
                                    message += "第【" + i + 1 + "】行,【银行类型】列,内容不正确.";
                                }

                                if (!string.IsNullOrEmpty(dt.Rows[i]["开户账户"].ToString()))
                                {
                                    entity.C_NUMBER = dt.Rows[i]["开户账户"].ToString();
                                }
                                else
                                {
                                    message += "第【" + i + 1 + "】行,【开户账户】列,内容不正确.";
                                }

                                if (!string.IsNullOrEmpty(dt.Rows[i]["开户姓名"].ToString()))
                                {
                                    entity.C_REALNAME = dt.Rows[i]["开户姓名"].ToString();
                                }
                                else
                                {
                                    message += "第【" + i + 1 + "】行,【开户姓名】列,内容不正确.";
                                }

                                entity.D_OPERATOR_DATE = DateTime.Now;
                                entity.N_OPERATOR_ID = CurrentUserID;

                                if (message == "")
                                {
                                    ls_bank.Add(entity);
                                }
                                else
                                {
                                    summessage += message;
                                }
                            }

                            if (summessage != "")
                            {
                                MessageBox.Show(Page, summessage);
                            }
                            else
                            {
                                foreach (Yx_CustomerBank bank in ls_bank)
                                {
                                    customerBankBLL.Insert(bank);
                                }
                                MessageBox.Show(Page, "导入成功");
                            }

                            #endregion 添加数据
                        }
                        else
                        {
                            MessageBox.Show(Page, "【提示】Excel里没有任何内容");
                            return;
                        }
                    }
                    else
                    {
                        MessageBox.Show(Page, "【提示】请选择正确的Excel格式");
                        return;
                    }
                }
                else
                {
                    MessageBox.Show(Page, "【提示】请选择导入文件!");
                    return;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, "导入Excel过程中出现未知错误!");
            }
        }

        protected void btnWrite_Click(object sender, EventArgs e)
        {
            string fileName = "银行信息批量导入模板";
            string excelHeader = string.Empty;
            string excelContent = string.Empty;

            DataTable dt = new DataTable();//获取数据源
            dt.Columns.Add("客户公司名称", typeof(string));
            dt.Columns.Add("银行类型", typeof(string));
            dt.Columns.Add("开户账户", typeof(string));
            dt.Columns.Add("开户姓名", typeof(string));

            DataRow[] dr = dt.Select();
            int colCount = dt.Columns.Count;
            string headName = string.Empty;

            //行标题
            for (int i = 0; i < colCount; i++)
            {
                headName = dt.Columns[i].ColumnName;
                if (i == colCount - 1)
                {
                    excelHeader += headName + "\n";
                }
                else
                {
                    excelHeader += headName + "\t";
                }
            }

            base.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            base.Response.ContentType = "application/vnd.ms-excel";
            base.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            base.Response.Write(excelHeader);
            base.Response.Write(excelContent);
            base.Response.Flush();
            base.Response.End();
        }

        /// <summary>
        /// GridView标题列排序
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void grvShow_Sorting(object sender, GridViewSortEventArgs e)
        {
            string sPage = e.SortExpression;
            string SortOrder, OrderDire;
            SortOrder = ViewState["SortOrder"] == null ? null : (string)ViewState["SortOrder"];
            OrderDire = ViewState["OrderDire"] == null ? null : (string)ViewState["OrderDire"];
            if (SortOrder == sPage)
            {
                if (OrderDire == "Desc")
                    OrderDire = "ASC";
                else
                    OrderDire = "Desc";
            }
            else
            {
                SortOrder = e.SortExpression;
                OrderDire = "ASC";
            }
            ViewState["SortOrder"] = SortOrder;
            ViewState["OrderDire"] = OrderDire;
            BindProjectName(true);
        }

        /// <summary>
        /// 绑定项目名称（非动态）绑定GridView
        /// </summary>
        public void BindProjectName(bool iSsort)
        {
            string where;
            if (FORM_COMPANY_ID.SelectedValue.ToString() == "-1")
            {
                if (lognUser.USER_LOGINNAME == "SUPER")
                {
                    where = "1 = 1";
                }
                else
                {
                    where = "T2.N_COMPANY_ID IN (" + companyBLL.GetCompanyFatherAndChildByID(CurrentUserCompanyID) + ")";
                }
            }
            else
            {
                where = "T2.N_COMPANY_ID = " + FORM_COMPANY_ID.SelectedValue;
            }
            if (FORM_STATE.SelectedValue != "-1")
            {
                where += " AND T2.N_STATE = " + FORM_STATE.SelectedValue;
            }

            var sql = @"SELECT
	T1.*,
	T2.C_NAME AS C_CUSTOMER_NAME,
	DECODE(T2.N_STATE,0,'停用','正常') AS C_STATE,
	T3.C_NAME AS C_BANK_NAME,
	T4.USER_NAME AS C_OPERATOR_NAME,
	T5.CMY_NAME AS C_COMPANY_NAME
FROM
	YX_CUSTOMER_BANK T1
LEFT JOIN YX_CUSTOMER T2 ON T1.N_CUSTOMER_ID = T2.N_ID
LEFT JOIN SYS_BANK T3 ON T1.N_BANK_ID = T3.N_ID
LEFT JOIN SYS_USER T4 ON T1.N_OPERATOR_ID = T4.USER_ID
LEFT JOIN SYS_COMPANY T5 ON T2.N_COMPANY_ID = T5.CMY_ID";
            sql += " WHERE " + where;

            PageIng.RowsCount = companyBLL.SqlCount(sql);

            var dt = companyBLL.PageQuery(sql, (PageIng.CurrentPage - 1) * PageIng.RowsPerPage, PageIng.RowsPerPage);
            if (dt.Rows.Count > 0)
            {
                PageIng.Visible = true;
                PageIng.DataSource = dt;
                PageIng.GridViewSource = grvShow;
                PageIng.RowsPerPage = PageIng._rowsPerPage;
                PageIng.Bind();
            }
            else
            {
                PageIng.DataSource = dt;
                PageIng.GridViewSource = grvShow;
                grvShow.DataSource = null;
                grvShow.DataBind();
                PageIng.Visible = false;
            }

            DataView view = dt.DefaultView;
            if (iSsort)
            {
                string sort = (string)ViewState["SortOrder"] + " " + (string)ViewState["OrderDire"];
                view.Sort = sort;//设置排序
            }

            grvShow.DataSource = view;

            grvShow.DataBind();
        }
    }
}